package com.snts.synchronization.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

import javax.sql.DataSource;

import org.apache.commons.lang.math.RandomUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.DisposableBean;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.jdbc.core.SqlTypeValue;

import com.snts.synchronization.ChangeManager;
import com.snts.synchronization.SyncService;
import com.snts.synchronization.collector.UpdateQueue;
import com.snts.synchronization.collector.UpdateQueueImpl;
import com.snts.synchronization.core.AuthRequest;
import com.snts.synchronization.core.AuthResponse;
import com.snts.synchronization.core.BaseResponse;
import com.snts.synchronization.core.BaseTable;
import com.snts.synchronization.core.Change;
import com.snts.synchronization.core.ChangeColumn;
import com.snts.synchronization.core.Column;
import com.snts.synchronization.core.ErrorObject;
import com.snts.synchronization.core.ResponseStatus;
import com.snts.synchronization.core.SyncTable.JoinRoute;
import com.snts.synchronization.core.Synchronization;
import com.snts.synchronization.core.SyncRequest;
import com.snts.synchronization.core.SyncResponse;
import com.snts.synchronization.core.SyncTable;
import com.snts.synchronization.core.TableReference;
import com.snts.synchronization.core.UUID;
import com.snts.synchronization.garbage.SynchronizationService;
import com.snts.synchronization.garbage.TransactionConfirmation;
import com.snts.synchronization.garbage.Utils;
import com.snts.synchronization.loader.ChangeManagerImpl;
import com.snts.synchronization.schema.SchemaUpdatesGenerator;
import com.snts.synchronization.server.SessionManager.Session;
import com.snts.synchronization.transaction.Transaction;

public class SyncServer implements SyncService, InitializingBean {

	private Synchronization synchronization = new Synchronization();
	private SessionManager sessionManager = new SessionManagerImpl();
	private DataSource dataSource;

	private ChangeManager changeManager;

	private String usersTableName = "restaurants";
	private String userIdColumn = "id";
	private String passwordColumn = "password";
	private boolean passwordIsMd5 = true;
	private static final Log log = LogFactory.getLog(SyncServer.class);

	private ThreadLocal<Session> session = new ThreadLocal<Session>();

	@Override
	public AuthResponse auth(AuthRequest authRequest) throws Exception {
		AuthResponse authResponse = new AuthResponse();
		try {
			Connection conn = dataSource.getConnection();
			try {
				PreparedStatement pstmt = conn.prepareStatement("select \"" + passwordColumn + "\" from \"" + usersTableName
						+ "\" where \"" + userIdColumn + "\" = ?");
				pstmt.setObject(1, java.util.UUID.fromString(authRequest.getUserName()));
				ResultSet rs = pstmt.executeQuery();
				String password = authRequest.getPassword();
				if (passwordIsMd5) {
					password = Utils.md5(password);
				}
				if (!rs.next() || !password.equals(rs.getString(passwordColumn))) {
					authResponse.setStatus(ResponseStatus.FAIL);
					authResponse.setErrorObject(new ErrorObject());
					authResponse.getErrorObject().setErrorCode(1);
					authResponse.getErrorObject().setErrorMessage("Invalid restaurant id or password.");
				} else {
					Session session = sessionManager.createSession();
					session.setRestaurantId(new UUID(authRequest.getUserName()));
					authResponse.setSessionId(session.getId());
					authResponse.setStatus(ResponseStatus.OK);
				}
			} finally {
				conn.close();
			}
		} catch (Throwable t) {
			authResponse.setStatus(ResponseStatus.FAIL);
			authResponse.setErrorObject(new ErrorObject());
			authResponse.getErrorObject().setErrorCode(2);
			authResponse.getErrorObject().setErrorMessage(t.getMessage());
			authResponse.getErrorObject().setThrowable(t);
		}
		return authResponse;
	}

	@Override
	public boolean ping(String sessionId) {
		return sessionManager.getSession(sessionId) != null;
	}

	@Override
	public SyncResponse sync(SyncRequest syncRequest) throws Exception {
		if (syncRequest.getSessionId() == null || "".equals(syncRequest.getSessionId())) {
			log.error("session id not set!");
			throw new Exception("session id not set!");
		}
		SyncResponse syncResponse = new SyncResponse();
		Session session = sessionManager.getSession(syncRequest.getSessionId());
		if (session == null) {
			log.error("Session " + syncRequest.getSessionId() + " not found!");
			log.error("Returning \"Session timeout\"");
			syncResponse.setStatus(ResponseStatus.FAIL);
			ErrorObject errorObject = new ErrorObject();
			errorObject.setErrorCode(3);
			errorObject.setErrorMessage("Session timeout");
			syncResponse.setErrorObject(errorObject);
			return syncResponse;
		} else {
			this.session.set(session);
		}
		try {
			Connection conn = dataSource.getConnection();
			try {
				conn.setAutoCommit(false);
				try {
					lockRows(syncRequest, conn);
					List<Change> changes = changeManager.getChanges(conn, session.getRestaurantId());
					resolveConflicts(syncRequest.getChanges(), changes);
					// setRestId(conn,
					// java.util.UUID.fromString(session.getRestaurantId().toString()));
					Statement stmt = conn.createStatement();
					for (Change change : syncRequest.getChanges()) {
						stmt.execute("ALTER TABLE " + change.getTable().getSchema() + ".\"" + change.getTable().getName()
								+ "\" DISABLE TRIGGER " + change.getTable().getTriggerName());
						log.debug("about to apply change[type=" + change.getType().name() + "]");
						if (Change.Type.INSERT == change.getType()) {
							applyInsert(conn, change);
						} else if (Change.Type.UPDATE == change.getType()) {
							applyUpdate(conn, change);
						}
						stmt.execute("ALTER TABLE " + change.getTable().getSchema() + ".\"" + change.getTable().getName()
								+ "\" ENABLE TRIGGER " + change.getTable().getTriggerName());
					}
					conn.commit();
					syncResponse.setStatus(ResponseStatus.OK);
					for (Change change : changes) {
						syncResponse.addChange(change);
					}
					return syncResponse;
				} catch (Exception ex) {
					log.error("Exception during sync", ex);
					conn.rollback();
					syncResponse.setStatus(ResponseStatus.FAIL);
					ErrorObject errorObject = new ErrorObject();
					errorObject.setErrorCode(3);
					errorObject.setErrorMessage("Session timeout");
					syncResponse.setErrorObject(errorObject);
				}
			} finally {
				conn.close();
			}
			return syncResponse;
		} finally {
			this.session.set(null);
		}
	}

	private void setRestId(Connection conn, java.util.UUID restId) throws SQLException {
		String sql = "SELECT setRestId(?)";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setObject(1, restId);
		ResultSet rs = pstmt.executeQuery();
		rs.close();
		pstmt.close();
	}

	@Override
	public void confirm(SyncResponse syncResponse) throws Exception {
		Connection conn = dataSource.getConnection();
		changeManager.deleteChanges(conn, syncResponse.getChanges());
	}

	public void lockRows(SyncRequest syncRequest, Connection conn) throws SQLException {
		log.debug("lockRows");
		for (Change change : syncRequest.getChanges()) {
			if (Change.Type.UPDATE == change.getType()) {
				log.debug("locking row [row-id=" + change.getRowId().toString() + "]");
				PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM " + change.getTable().getSchema() + ".\""
						+ change.getTable().getName() + "\" WHERE id=? FOR UPDATE");
				pstmt.setObject(1, java.util.UUID.fromString(change.getRowId().toString()));
				ResultSet rs = pstmt.executeQuery();
				rs.next();
				rs.close();
			}
		}
	}

	private String generateUpdateSQL(Change change) throws SQLException {
		StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(change.getTable().getSchema());
		sql.append(".\"");
		sql.append(change.getTable().getName());
		sql.append("\" SET \n");
		for (ChangeColumn changeColumn : change.getColumns()) {
			sql.append(changeColumn.getColumn().getName());
			sql.append("=?");
			if (changeColumn != change.getColumns().get(change.getColumns().size() - 1)) {
				sql.append(",");
			}
		}
		sql.append(" WHERE id=?");
		return sql.toString();
	}

	private String generateInsertSQL(Change change) throws SQLException {
		StringBuffer sql = new StringBuffer("INSERT INTO " + change.getTable().getSchema() + ".\"" + change.getTable().getName() + "\"(\""
				+ synchronization.getPkColumnName() + "\",");
		for (int i = 0; i < change.getColumns().size(); i++) {
			sql.append("\"" + change.getColumns().get(i).getColumn().getName() + "\"");
			if (i < change.getColumns().size() - 1) {
				sql.append(",");
			}
		}
		TableReference ftr = change.getTable().getJoinRoute().getFirstTableReference();
		JoinRoute jr = change.getTable().getJoinRoute();
		if (jr.isRestaurantId()) {
			sql.append("," + ftr.getForeignKey());
		}
		sql.append(") VALUES (");
		sql.append("?,");
		for (int i = 0; i < change.getColumns().size(); i++) {
			sql.append("?");
			if (i < change.getColumns().size() - 1) {
				sql.append(",");
			}
		}
		if (jr.isRestaurantId()) {
			sql.append(",?");
		}
		sql.append(");");
		return sql.toString();
	}

	private void applyUpdate(Connection conn, Change change) throws SQLException {
		String updateSQL = generateUpdateSQL(change);
		PreparedStatement pstmt = conn.prepareStatement(updateSQL);
		int paramIndex = 1;
		for (ChangeColumn changeColumn : change.getColumns()) {
			changeColumn.setParam(pstmt, paramIndex++);
		}
		pstmt.setObject(paramIndex, java.util.UUID.fromString(change.getRowId().toString()));
		pstmt.executeUpdate();
	}

	private void applyInsert(Connection conn, Change change) throws SQLException {
		String insertSQL = generateInsertSQL(change);
		log.debug("applyInsert [insertSQL=[\n" + insertSQL + "\n]");
		PreparedStatement pstmt = conn.prepareStatement(insertSQL);
		int paramIndex = 1;
		pstmt.setObject(paramIndex++, java.util.UUID.fromString(change.getRowId().toString()));
		for (ChangeColumn changeColumn : change.getColumns()) {
			log.debug("Setting value for parameter number " + paramIndex + " value " + changeColumn.getValue().toString());
			changeColumn.setParam(pstmt, paramIndex++);
		}
		JoinRoute jr = change.getTable().getJoinRoute();
		if(jr.isRestaurantId()){
			pstmt.setObject(paramIndex++, java.util.UUID.fromString(session.get().getRestaurantId().toString()));
		}
		pstmt.executeUpdate();
	}

	private void resolveConflicts(List<Change> incoming, List<Change> outcoming) {
		for (Change iChange : incoming) {
			for (Change oChange : outcoming) {
				if (iChange.getRowId().equals(oChange.getRowId())) {
					for (ChangeColumn ichColumn : iChange.getColumns()) {
						ChangeColumn ochColumn = oChange.getColumnByName(ichColumn.getColumn().getName());
						if (ochColumn != null) {
							ochColumn.setOn(false);
						}
					}
				}
			}
		}
	}

	public Synchronization getSynchronization() {
		return synchronization;
	}

	public void setSynchronization(Synchronization synchronization) {
		this.synchronization = synchronization;
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public String getUsersTableName() {
		return usersTableName;
	}

	public void setUsersTableName(String usersTableName) {
		this.usersTableName = usersTableName;
	}

	public String getUserIdColumn() {
		return userIdColumn;
	}

	public void setUserIdColumn(String userIdColumn) {
		this.userIdColumn = userIdColumn;
	}

	public String getPasswordColumn() {
		return passwordColumn;
	}

	public void setPasswordColumn(String passwordColumn) {
		this.passwordColumn = passwordColumn;
	}

	public boolean isPasswordIsMd5() {
		return passwordIsMd5;
	}

	public void setPasswordIsMd5(boolean passwordIsMd5) {
		this.passwordIsMd5 = passwordIsMd5;
	}

	public SessionManager getSessionManager() {
		return sessionManager;
	}

	public void setSessionManager(SessionManager sessionManager) {
		this.sessionManager = sessionManager;
	}

	public void setChangeManager(ChangeManager changeLoader) {
		this.changeManager = changeLoader;
	}

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Class.forName("org.postgresql.Driver");
		Connection conn = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/rgenius", "postgres", "123");
		Synchronization synchronization = new Synchronization();
		synchronization.load(conn.getMetaData());
		SyncTable syncTable = synchronization.getTables().get(0);

		Change change = new Change(Change.Type.INSERT);
		change.setTable(syncTable);
		change.setRowId(new UUID("0714b54e-b9df-11e1-8399-efdd549c38b6"));

		for (Column column : syncTable.getColumns()) {
			ChangeColumn changeColumn = change.addColumn(column);
			if (column.getSqlType() == Types.BOOLEAN) {
				changeColumn.setValue(true);
			} else {
				changeColumn.setValue("TEST VALUE");
			}
		}
		// System.out.println(new SyncServer().generateUpdateSQL(change));
		System.out.println(new SyncServer().generateInsertSQL(change));
	}

	@Override
	public void afterPropertiesSet() throws Exception {
		updateSchema();
	}

	private void updateSchema() throws SQLException {
		Connection conn = dataSource.getConnection();
		try {
			new SchemaUpdatesGenerator().installUpdates(conn, synchronization);
		} finally {
			conn.close();
		}
	}

}
